<?php
class Shepherd_Model_Mapper_Container extends Shepherd_Model_Mapper_Abstract
{
	protected $_dbName;
	
	public function __construct()
	{
		parent::__construct();
		$this->_dbName = $this->getCurrentShepherdSchema().'.containers';
	}
	/**
	 * Get Db table object
	 *
	 * @return Model_DbTable_Container
	 */
	public function getDbTable($instanceId = null)
	{
		if (null === $this->_dbTable) {
			$this->setDbTable('Shepherd_Model_DbTable_Container',$instanceId);
		}
		return $this->_dbTable;
	}
	
	/**
	 * Map row object data to the site object properties
	 *
	 * @param  Zend_Db_Table_Row $row
	 * @throws Exception
	 * @return Model_Container mapped object
	 */
	public function mapData($row)
	{
		if (!$row instanceof Zend_Db_Table_Row) {
			throw new Exception('Invalid table row provided');
		}
		$container = new Shepherd_Model_Container();
		$container->setContainerId($row['containerid'])
		->setContainerName($row['containername'])
		->setPhoto($row['photo'])
		->setIsused($row['isused'])
		->setCreatedBy($row['created_by'])
		->setUpdatedBy($row['updated_by'])
		->setCreatedDate($row['created_date'])
		->setUpdatedDate($row['updated_date'])
		->setParentId($row['parentid'])
		->setWidth($row['width'])
		->setHeight($row['height'])
		->setCompressorType($row['compressor_type'])
		->setLastestRevision($row['lastest_revision'])
		->setRoomValue($row['room_value'])
		->setIsolationMaterial($row['isolation_material'])
		->setCompressor($row['compressor'])
		->setStockLocation($row['stocklocation'])
		->setTopValue($row['top_value'])
		->setLeftValue($row['left_value'])
		->setPhotoFloorplan($row['photofloorplan']);
		return $container;
	}
	
	/**
	 * Save Container object in database
	 *
	 * @param Model_Container $container
	 *
	 * @return int The primary key of the row inserted or the number of rows updated
	 */
	public function save($container,$instanceId = null)
	{
		parent::save($container);
		$data = array(
				'containerid'        => $container->getContainerId(),
				'containername'      => $container->getContainerName(),
				'photo'              => $container->getPhoto(),
				'isused'             => $container->getIsused(),
				'created_by'         => $container->getCreatedBy(),
				'updated_by'         => $container->getUpdatedBy(),
				'created_date'       => $container->getCreatedDate(),
				'updated_date'       => $container->getUpdatedDate(),
				'parentid'           => $container->getParentId(),
				'width'              => $container->getWidth(),
				'height'             => $container->getHeight(),
				'compressor_type'    => $container->getCompressorType(),
				'lastest_revision'   => $container->getLastestRevision(),
				'room_value'         => $container->getRoomValue(),
				'isolation_material' => $container->getIsolationMaterial(),
				'compressor'         => $container->getCompressor(),
				'stocklocation'      => $container->getStockLocation(),
				'top_value'          => $container->getTopValue(),
				'left_value'         => $container->getLeftValue(),
				'photofloorplan'     => $container->getPhotoFloorPlan()
		);
		try {
			if (NULL === ($id = $container->getContainerId())) {
				unset($data['containerid']);
				$container->setContainerId($this->getDbTable($instanceId)->insert($data));
				return $container->getContainerId();
			} else {
				return $this->getDbTable($instanceId)->update($data, array('containerid = ?' => $id));
			}
		} catch (Exception $e) {
			Sss_Ultility_Common::logger()->log($e, Zend_Log::ERR);
		}
	}
	
	/**
	 * Fetch all Container
	 *
	 * @return array List of Model_Container
	 */
	public function fetchAll($instanceId = null)
	{
		$resultSet = $this->getDbTable($instanceId)->fetchAll();
		$entries   = array();
		foreach ($resultSet as $row) {
			$entries[] = $this->mapData($row);
		}
		return $entries;
	}
	
	/**
	 * Fetch a Site Container by Identifier
	 *
	 * @param $id
	 *
	 * @return Model_Container
	 */
	public function findById($id,$instanceId = null)
	{
		$select	= $this->getDbTable($instanceId)->select()->where('containerid = ?', $id);
		$row = $this->getDbTable()->fetchRow($select);
		$entry   = NULL;
		if (!empty($row)) {
			$entry = $this->mapData($row);
		}
		return $entry;
	}

	public function deleteRow($containerId,$instanceId = null)
	{
		return $this->getDbTable($instanceId)->delete(array('containerid = ?' => $containerId));
	}

	/**
	* get room list
	* 
	* @param mixed $limit
	* @param mixed $limitstart
	* @param mixed $filterParam
	* @return stdclass
	*/
	public function getRoomList($limit = '', $limitstart = 0, $filterParam = array())
	{
		//initializa where 
		$where = array();

		if ((int) $filterParam['floorplan']) {
			$where[] = 'ct.parentid=' . (int) $filterParam['floorplan'];
		}
		$where[] = 'ct.isused = 1';
		$where   = count($where) ? 'WHERE ' . implode(' AND ', $where) : '';
		//initializa order by
		$orderBy = '';

		if(is_null($filterParam['orderfield']))
		{
			$filterParam['orderfield'] = 'ct.containerid';
		}
		if (!is_null($filterParam['orderfield1'])) {
			$typeOrder = !is_null($filterParam['ordertype1']) ? $filterParam['ordertype1'] : 'desc';
			$orderBy   = ' ORDER BY ' . $filterParam['orderfield1'] . ' ' . $typeOrder;
		}

		//initializa pagination
		$pagString = '';
		if ($limit == '')
			$limit = Sss_Ultility_Setting::PAGE_SIZE;
		if (isset($filterParam['page1'])) {
			if ($filterParam['page1'] == null)
				$limitstart = 0;
			else
				$limitstart = $limit * ($filterParam['page1'] - 1);
		} else {
			$limitstart = 0;  
		}
		if ($limit < 0) {
			$pagString = '';
		} else {
			$pagString = ' LIMIT ' . $limitstart . ' , ' . $limit;	
		}

		//get all with paging
		$rows = $this->loadAll('
								SELECT 
									ct.containerid,
									ct.containername 
								FROM 
									' . $this->_dbName . ' ct
		' . $where . $orderBy . $pagString,true);

		//get all record not paging
		$rowAll = $this->loadAll('
								SELECT 
									ct.containerid,
									ct.containername 
								FROM 
									' . $this->_dbName . ' ct
		' . $where . $orderBy,true);
		$result = new stdclass();
		//return pagination object
		$result->pagination = Sss_Grid::createPagiObject($limit, $limitstart, count($rowAll));

		//return list
		$result->list       = $rows;
		return $result;
	}
	
	/**
	* get photo
	* 
	* @param mixed $filterParam
	* @return mixed
	*/
	public function getContainer($containerId = 0, $instanceId=null, $stockLocation = null, $isParent = null)
	{
		$containerTableName = '';

		if (is_null($instanceId)) {
			$containerTableName = $this->getCurrentShepherdSchema().'.containers';
		} else {
			$containerTableName = SHEPHERD_SUFIX . Sss_Ultility_Common::formatString($instanceId,Sss_Ultility_Setting::FORMAT_NUMBER) . '.containers';
			$checkSQL = "
						SELECT 
							SCHEMA_NAME 
						FROM 
							INFORMATION_SCHEMA.SCHEMATA 
						WHERE 
							SCHEMA_NAME = '".SHEPHERD_SUFIX . Sss_Ultility_Common::formatString($instanceId,Sss_Ultility_Setting::FORMAT_NUMBER) . "'";
			$res = $this->fetchObject($checkSQL);
			if (!$res) {
				return false;
			}
		}
		//initializa where 
		$where = array();
		if($stockLocation != null){
			if($isParent == 0){
				$where[] = 'co.stocklocation=' . (int) $stockLocation;
			}elseif($isParent > 0){
				$where[] = 'pco.stocklocation=' . (int) $stockLocation;
			}
		}
		if($isParent != null)
		{
			if($isParent == 0){
				$select = '
							SELECT 
								co.* 
							FROM 
								'.$containerTableName.' co 
								INNER JOIN '.$this->_db_ovis_name.'.stocklocationinstances si ON co.stocklocation = si.stocklocation';
			} elseif($isParent > 0){
				$select = '
							SELECT 
								co.* 
							FROM '.$this->_dbName.' pco 
								LEFT JOIN '.$this->_dbName.' co ON pco.containerid = co.parentid 
								INNER JOIN '.$this->_db_ovis_name.'.stocklocationinstances si ON pco.stocklocation = si.stocklocation';
			}else{
				$select = '
							SELECT 
								* 
							FROM '. $containerTableName .' co ';
			}
		}else{
			$select = '
						SELECT 
							* 
						FROM 
							'. $containerTableName. ' co '; 
		}
		if ((int) $containerId) {
			if($isParent != null){
				$where[] = 'co.containerid=' . (int) $containerId;   
			}else{
				$where[] = 'containerid=' . (int) $containerId;   
			}

		}
		if($isParent != null){
			$where[] = 'co.isused = 1';
		}else{
			$where[] = 'isused = 1';
		}
		$where   = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';
		$limit = " limit 1 ";
		//get string photo
		$container = $this->fetchObject($select . ' ' . $where . $limit);

		return $container;
	}

	public function getAllRoomByIsUsed($stockLocation = null){
		if($stockLocation != null){
			$clause = ' AND pco.stocklocation = '.$stockLocation;
		}else{
			$clause = '';
		}
		$select = "
					SELECT 
						co.*,s.stocklocationname, 
						al.sensornumber 
					FROM 
						".$this->_dbName . " AS co 
						LEFT JOIN (
									SELECT 
										a.destination, 
										COUNT(a.destination) AS `sensornumber` 
									FROM 
										" . $this->getCurrentShepherdSchema() . ".allocations a 
										INNER JOIN " . $this->_db_ovis_name .".hardware h ON h.hardwareid = a.hardware 
									WHERE 
										a.systemname='" . Sss_Ultility_SystemName::ROOMS . "' 
										AND (h.status = " . Sss_Ultility_Setting::HARDWARE_STATUS_ACTIVE. " OR h.status = " . Sss_Ultility_Setting::HARDWARE_STATUS_DETACH . ") 
										AND a.status = 1 
									GROUP BY 
										a.destination
									) al ON co.containerid=al.destination 
						LEFT JOIN ".$this->_dbName." pco ON pco.containerid = co.parentid
						LEFT JOIN ".$this->_db_ovis_name.".stocklocations s ON s.stocklocationid = pco.stocklocation
						INNER JOIN ".$this->_db_ovis_name.".stocklocationinstances si ON s.stocklocationid = si.stocklocation";
		$where  = "
					WHERE 
						co.parentid <> 0 
						AND co.isused=1 $clause 
					ORDER BY 
						co.containerid 
					DESC";  
		return $this->loadAll($select.' '.$where, true);
	}
	
	public function getRoomByFloorplan($flooplan)
	{
		if($flooplan == 0) {
			$select = '
						SELECT 
							co.* 
						FROM 
							'.$this->_dbName.' co 
							INNER JOIN '.$this->_db_ovis_name.'.stocklocationinstances si ON co.stocklocation = si.stocklocation';
			$where  = ' WHERE 
							co.isused=1 and co.parentid = '.$flooplan.' 
						ORDER BY 
							co.containerid 
						DESC';
		}else{
			$select = '
					SELECT 
						pco.* 
					FROM 
						'.$this->_dbName.' co 
						LEFT JOIN '.$this->_dbName.' pco ON co.containerid = pco.parentid 
						INNER JOIN '.$this->_db_ovis_name.'.stocklocationinstances si ON co.stocklocation = si.stocklocation';
			$where = 'WHERE 
						pco.isused=1 
						AND pco.parentid = '.$flooplan.' 
						ORDER BY pco.containerid 
						DESC';
		}
		return $this->loadAll($select.' '.$where, true);
	}
	
	public function getFloorplanByStockLocation($flooplan, $stocklocation = null)
	{
		$select = '
					SELECT 
						* 
					FROM 
						'.$this->_dbName. ' co 
						INNER JOIN '.$this->_db_ovis_name.'.stocklocationinstances si ON co.stocklocation = si.stocklocation';
		if($stocklocation != null){
			$subWhere = ' AND co.stocklocation ='.$stocklocation;
		} else {
			$subWhere = '';
		}
		$where = '
					WHERE 
						co.isused=1 
						AND co.parentid = '.$flooplan.$subWhere. '
					ORDER BY 
						co.containerid 
					DESC';
		return $this->loadAll($select.' '.$where, true);
	}
	
	public function getContainerList($limit = '', $limitstart = 0, $filterParam = array(), $instanceId=null,$type=null)
	{
		$containerTableName = '';
		$subWhere = '';
		if (is_null($instanceId)) {
			$containerTableName = $this->getCurrentShepherdSchema().'.containers';
		} else {
			$containerTableName = SHEPHERD_SUFIX . Sss_Ultility_Common::formatString($instanceId,Sss_Ultility_Setting::FORMAT_NUMBER) . '.containers';
			$checkSQL = "
						SELECT 
							SCHEMA_NAME 
						FROM 
							INFORMATION_SCHEMA.SCHEMATA 
						WHERE 
							SCHEMA_NAME = '".SHEPHERD_SUFIX . Sss_Ultility_Common::formatString($instanceId,Sss_Ultility_Setting::FORMAT_NUMBER) . "'";
			$res = $this->fetchObject($checkSQL);
			if (!$res) {
				return false;
			}
		}
		$where = array();
		$selectStockName = '';
		if (isset($filterParam['parentid'])) {
			$where[] = 'ct.parentid=' . (int) $filterParam['parentid'];
			if($filterParam['parentid'] <= 0){
				$subWhere = " INNER JOIN ".$this->_db_ovis_name.".stocklocationinstances si ON ct.stocklocation = si.stocklocation ";
				if($type == 1) {
					$selectStockName = ',st.stocklocationname';	
					$subWhere .= " INNER JOIN ".$this->_db_ovis_name.".stocklocations st ON st.stocklocationid = ct.stocklocation";
				}
			}
		}
		if (isset($filterParam['containername'])) {
			$where[] = 'ct.containername LIKE "%' . $filterParam['containername'] . '%"';
		}
		$where[] = 'ct.isused = 1';
		$where   = count($where) ? 'WHERE ' . implode(' AND ', $where) : '';
		//initializa order by
		$orderBy = '';
		if(is_null($instanceId) && $type == 1 && is_null($filterParam['orderfield'])){ 
			$filterParam['orderfield'] = 'st.stocklocationname,ct.containername';
		} else {
			if (is_null($filterParam['orderfield'])) {
				$filterParam['orderfield'] = 'ct.containername';
			}
		}
		if (!is_null($filterParam['orderfield'])) {
			$typeOrder = !is_null($filterParam['ordertype']) ? $filterParam['ordertype'] : 'asc'; 
			$orderBy   = ' ORDER BY ' . $filterParam['orderfield'] . ' ' . $typeOrder;
		}
		//initializa pagination
		$pagString = '';
		if ($limit == '')
			$limit = Sss_Ultility_Setting::PAGE_SIZE;
		if ($filterParam['page'] == null) {
			$limitstart = 0;
		} else {
			$limitstart = $limit * ($filterParam['page'] - 1);
		}
		if ($limit < 0) {
			$pagString = '';
		} else {
			$pagString = ' LIMIT ' . $limitstart . ' , ' . $limit;	
		}
		$query = "
				SELECT 
					ct.containerid,ct.containername,ct.photo, ct.top_value, 
					ct.left_value".$selectStockName." 
				FROM 
					".$containerTableName." ct $subWhere " 
				. $where 
				. $orderBy;

		//get all with paging
		$rows = $this->loadAll( $query.$pagString);
		//get all
		$rowAll = $this->loadAll($query);
		$result = new stdclass();
		//return pagination object
		$result->pagination = Sss_Grid::createPagiObject($limit, $limitstart, count($rowAll));
		//return list
		$result->list       = $rows;
		return $result;
	}
	
	public function checkRoomsInFloorPlan($parent = 0)
	{
		//initializa where 
		$where = '';
		if ((int) $parent) {
			$where = 'parentid=' . (int) $parent;
		}
		$where   = count($where) ? ' WHERE ' . $where : '';

		$rows = $this->fetchObject('
									SELECT 
										* 
									FROM 
										'.$this->_dbName
									. $where
									);
		if(!$rows) {
			return true;
		} else {
			return false;
		}
	}
	
	public function getRoomsByParentId($parent = 0, $instanceId=null)
	{
		$containerTableName = '';
		if (is_null($instanceId)) {
			$containerTableName = $this->getCurrentShepherdSchema().'.containers';
		} else {
			$containerTableName = SHEPHERD_SUFIX . Sss_Ultility_Common::formatString($instanceId,Sss_Ultility_Setting::FORMAT_NUMBER) . '.containers';
			$checkSQL = "
						SELECT 
							SCHEMA_NAME 
						FROM 
							INFORMATION_SCHEMA.SCHEMATA 
						WHERE 
							SCHEMA_NAME = '".SHEPHERD_SUFIX . Sss_Ultility_Common::formatString($instanceId,Sss_Ultility_Setting::FORMAT_NUMBER) . "'";
			$res = $this->fetchObject($checkSQL);
			if (!$res) {
				return false;
			}
		}
		$where = '';
		if ((int) $parent) {
			$where = 'parentid=' . (int) $parent;
		}   
		$where   = count($where) ? ' WHERE ' . $where : '';
		$rows = $this->loadAll('
								SELECT 
									* 
								FROM 
									'.$containerTableName.'
								' 
								. $where
								);
		$result = new stdclass();
		$result->list       = $rows;
		return $result;
	}

	public function checkEsixtedColumns($columnName = '', $instanceId=null) 
	{
		$databaseName = $this->getCurrentShepherdSchema($instanceId);            

		$query = "
					SHOW COLUMNS 
					FROM 
						$databaseName.`containers` 
					LIKE '".$columnName."'";
		$res = $this->fetchObject($query, true, $instanceId);
		if($res == false)
		{
			$addCol = " ALTER TABLE 
							$databaseName.`containers` 
						ADD COLUMN ".$columnName." 
						VARCHAR (255)";
			$this->executeQuery($addCol, true, $instanceId);
		}        
	}
}